package com.shuhe.handler;

import com.alibaba.fastjson.JSONObject;
import com.shuhe.domain.mxgc.SqlResponseResult;
import com.shuhe.domain.mxgc.TreeResult;
import com.shuhe.domain.report.ReportDataResponse;
import com.shuhe.metastore.DBAppSql;
import com.shuhe.metastore.DBJrSql;
import com.shuhe.metastore.DBSql;
import com.shuhe.metastore.MetaMysql;
import org.apache.log4j.Logger;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;

import static com.shuhe.common.CommonFunction.*;
import static com.shuhe.handler.ReportHandler.makeDataResp;
import static com.shuhe.handler.ServiceDetail.setSuccessBodyByResult;

public class EditTableHandler extends HttpServlet {

    private static Logger logger = Logger.getLogger(EditTableHandler.class);

    protected void doOptions(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp = setResponse(resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        try {
            req.setCharacterEncoding("UTF-8");
            resp = setResponse(resp);
            String bodyStr = readAsChars(req);
            JSONObject bodyJson = JSONObject.parseObject(bodyStr);
            String operateType = bodyJson.getString("operateType");
            if (operateType.equals("queryTable")) {
                //查询
                String sqlResp = queryTable(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }
            if (operateType.equals("queryBySql")) {
                //查询，返回列表
                String sqlResp = queryBySql(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }
            if (operateType.equals("querySqlGetTree")) {
                //查询，返回树
                String sqlResp = querySqlGetTree(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }
            if (operateType.equals("saveTable")) {
                //保存 按DATA
                String sqlResp = saveTable(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }
            if (operateType.equals("saveTableBySql")) {
                //保存 按SQL
                String sqlResp = saveTableBySql(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }
            if (operateType.equals("deleteRecord")) {
                //删除 按ID
                String sqlResp = deleteRecord(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }
            if (operateType.equals("deleteRecordBySql")) {
                //删除 按SQL
                String sqlResp = deleteRecordBySql(bodyJson);
                resp.getWriter().write(sqlResp);
                logger.info(sqlResp);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            logger.info(ex.getMessage());
            resp.getWriter().write(returnFail(ex.getMessage()));
        }
    }


    //查询，返回树
    public static String querySqlGetTree(JSONObject bodyJson) throws Exception {
        Connection metaConn = null;
        try {
            String dbType = bodyJson.getString("dbType");
            String sql = bodyJson.getString("sql");
            String subSql = bodyJson.getString("subSql");
            if (dbType.equals("meta")) {
                metaConn = MetaMysql.connect();
            } else {
                metaConn = DBSql.connect();
            }
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            ArrayList<TreeResult> treeResults = new ArrayList<>();
            while (rs.next()) {
                treeResults.add(getTree(metaConn, rs, subSql));
            }
            ReportDataResponse resp = new ReportDataResponse();
            resp.setData(treeResults);
            long beginTime = Calendar.getInstance().getTimeInMillis();
            resp.setCommon(getCommonResponseBody(beginTime));
            String respJson = JSONObject.toJSONString(resp);
            return respJson;
        } finally {
            closeDb(metaConn);
        }
    }

    public static TreeResult getTree(Connection metaConn, ResultSet rs, String subSql) throws Exception {
        TreeResult tr = new TreeResult();
        tr.setKey(rs.getString("id"));
        tr.setValue(rs.getString("id"));
        tr.setTitle(rs.getString("title"));
        tr.setShowOrder(rs.getString("show_order"));
        tr.setParentId(rs.getString("parent_id"));
        tr.setParentMc(rs.getString("parent_mc"));
        tr.setChildren(getSubTree(metaConn, rs.getString("id"), subSql));
        return tr;
    }

    public static ArrayList<Object> getSubTree(Connection metaConn, String parentId, String subSql) throws Exception {
        String sql = subSql.replace("{parent_id}", parentId);
        ResultSet rs = MetaMysql.querySql(metaConn, sql);
        ArrayList<Object> children = new ArrayList<>();
        while (rs.next()) {
            children.add(getTree(metaConn, rs, subSql));
        }
        return children;
    }

    public static String queryBySql(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            String sql = bodyJson.getString("sql");
            String dbType = bodyJson.getString("dbType");
            String current = bodyJson.getString("current");
            String pageSize = bodyJson.getString("pageSize");
            dbConn = getConn(dbType);
            String countSql = "select count(*) as cun from (" + sql + ") a";
            //返回行数
            int beginNum = (Integer.parseInt(current) - 1) * Integer.parseInt(pageSize);
            if (dbConn.getClass().getName().equals("org.postgresql.jdbc.PgConnection")) {
                sql = sql + " limit " + pageSize + " offset" + " " + String.valueOf(beginNum);
            } else {
                sql = sql + " limit " + String.valueOf(beginNum) + ", " + pageSize;
            }
            long beginTime = Calendar.getInstance().getTimeInMillis();
            return makeDataResp(dbConn, sql, current, beginTime, countSql);
        } finally {
            closeDb(dbConn);
        }
    }

    public static String queryTable(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            String tableName = bodyJson.getString("tableName");
            String userName = bodyJson.getString("userName");
            String whereStr = bodyJson.getString("whereStr");
            String orderStr = bodyJson.getString("orderStr");
            String dbType = bodyJson.getString("dbType");
            String current = bodyJson.getString("current");
            String pageSize = bodyJson.getString("pageSize");
            dbConn = getConn(dbType);
            String sql = "select * from " + tableName;
            if (userName != null && !userName.equals("")) {
                sql = sql + " where user_name = '" + userName + "'";
            } else {
                sql = sql + " where 1=1";
            }
            if (whereStr != null && !whereStr.equals("")) {
                sql = sql + " and " + whereStr;
            }
            String countSql = "select count(*) as cun from (" + sql + ") a";
            //返回行数
            int beginNum = (Integer.parseInt(current) - 1) * Integer.parseInt(pageSize);

            //增加排序功能
            sql = sql + " order by " + orderStr;
            //判断数据库类型
            if (dbConn.getClass().getName().equals("org.postgresql.jdbc.PgConnection")) {
                sql = sql + " limit " + pageSize + " offset" + " " + String.valueOf(beginNum);
            } else {
                sql = sql + " limit " + String.valueOf(beginNum) + ", " + pageSize;
            }
            long beginTime = Calendar.getInstance().getTimeInMillis();
            return makeDataResp(dbConn, sql, current, beginTime, countSql);
        } finally {
            closeDb(dbConn);
        }
    }

    public static String deleteRecord(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            String tableName = bodyJson.getString("tableName");
            String userName = bodyJson.getString("userName");
            String id = bodyJson.getString("id");
            String dbType = bodyJson.getString("dbType");
            dbConn = getConn(dbType);
            String sql = "delete from " + tableName + " where id = '" + id + "' and user_name = '" + userName + "'";
            DBSql.execSql(dbConn, sql);
            SqlResponseResult result = new SqlResponseResult();
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(dbConn);
        }
    }

    public static Connection getConn(String dbType) throws Exception {
        Connection dbConn = null;
        switch (dbType) {
            case "meta":
                dbConn = MetaMysql.connect();
                break;
            case "db":
                dbConn = DBSql.connect();
                break;
            case "jr":
                dbConn = DBJrSql.connect();
                break;
            case "app":
                dbConn = DBAppSql.connect();
                break;
        }
        return dbConn;
    }


    public static String deleteRecordBySql(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            String sql = bodyJson.getString("sql");
            String dbType = bodyJson.getString("dbType");
            String sqlIfNextSql = bodyJson.getString("sqlIfNextSql");
            dbConn = getConn(dbType);

            //判断是否可以删除
            if (sqlIfNextSql != null && !sqlIfNextSql.equals("")) {
                ResultSet rs = DBSql.querySql(dbConn, sqlIfNextSql);
                Boolean isExist = false;
                if (rs.next()) {
                    isExist = true;
                }
                if (isExist) {
                    return returnFail("有后续任务，不能删除");
                }
            }
            String[] sql_arr = sql.split(";");
            ArrayList sqls = new ArrayList();
            for (int i = 0; i < sql_arr.length; i++) {
                if (sql_arr[i].trim().length() > 0) {
                    sqls.add(sql_arr[i]);
                }
            }
            DBSql.batchExecSql(dbConn, sqls);
            SqlResponseResult result = new SqlResponseResult();
            String resultEnd = setSuccessBodyByResult(result);
            return resultEnd;
        } finally {
            closeDb(dbConn);
        }
    }

    public static String saveTable(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            String tableName = bodyJson.getString("tableName");
            JSONObject data = bodyJson.getJSONObject("data");
            String userName = bodyJson.getString("userName");
            String dbType = bodyJson.getString("dbType");
            dbConn = getConn(dbType);
            ArrayList<String> sqls = new ArrayList<>();

            String sql = "insert into " + tableName + "(";
            String valueStr = "";
            String id = "";
            for (String key : data.keySet()) {
                if (key.equals("index")) {
                    continue;
                }
                if (key.equals("id")) {
                    id = data.getString(key);
                }
                sql = sql + key + ",";
                String value = data.getString(key);
                value = value.replaceAll("'", "''");
                valueStr = valueStr + "'" + value + "',";
            }
            sql = sql.substring(0, sql.length() - 1) + ") values(" + valueStr.substring(0, valueStr.length() - 1) + ")";
            if (userName != null && !userName.equals("")) {
                sqls.add("delete from " + tableName + " where user_name = '" + userName + "' and id = '" + id + "'");
            } else {
                sqls.add("delete from " + tableName + " where id = '" + id + "'");
            }

            sqls.add(sql);
            DBSql.batchExecSql(dbConn, sqls);
            SqlResponseResult result = new SqlResponseResult();
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(dbConn);
        }
    }

    public static String saveTableBySql(JSONObject bodyJson) throws Exception {
        Connection dbConn = null;
        try {
            String delSql = bodyJson.getString("delSql");
            String insertSql = bodyJson.getString("insertSql");
            String dbType = bodyJson.getString("dbType");
            dbConn = getConn(dbType);
            ArrayList<String> sqls = new ArrayList<>();
            if (delSql.length() > 0) {
                sqls.add(delSql);
            }
            String[] sql_arr = insertSql.split(";");
            for (int i = 0; i < sql_arr.length; i++) {
                if (sql_arr[i].length() > 0) {
                    sqls.add(sql_arr[i]);
                }
            }
            DBSql.batchExecSql(dbConn, sqls);
            SqlResponseResult result = new SqlResponseResult();
            return setSuccessBodyByResult(result);
        } finally {
            closeDb(dbConn);
        }
    }

    public static void main(String[] args) throws Exception {
    }
}
